﻿using Newtonsoft.Json;
using Newtonsoft.Json.Serialization;
using SpreadsheetGear;
using Sustainalytics.ClientDW.Entities;
using Sustainalytics.DataLayer.ConnectionStrings;
using Sustainalytics.DataLayer.Shared;
using Sustainalytics.Entities.ConnectionStrings;
using Sustainalytics.Entities.ESGRatings;
using Sustainalytics.LongRunningTaskManager;
using Sustainalytics.LongRunningTaskManager.Interface;
using Sustainalytics.ReportGenerator.BL;
using Sustainalytics.Utils;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Diagnostics;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Net.Http;
using System.Net.Http.Formatting;
using System.Net.Http.Headers;
using WebApiContrib.Formatting;

namespace Sustainalytics.ESGRatings.Tasks
{
    using BusinessResearchProductStates.Entities.ResearchState;
    using Sustainalytics.EsgRatings.BusinessLogic;
    using Sustainalytics.ESGRatings.DataContract.ESG;
    using Sustainalytics.ESGRatings.Tasks.Models;

    public class ESGRatingDataExportTask : ILongRunningTask
    {
        public ESGRatingDataExportTask()
        {
            Type = LongRunningTaskTypeEnum.ESGRatingAdminCheckReport;
        }

        public LongRunningTaskTypeEnum Type { get; private set; }

        public object Start(LongRunningTaskContext context)
        {
            var options = JsonConvert.DeserializeObject<TestESGRatingInDto>(context.ParamJson);

            // get connection string to clientdw
            var cnxClientPlatform = ConfigurationManager.ConnectionStrings["ClientPlatform"].ConnectionString;
            var connectionStringsManager = ConnectionStringsManager.GetInstance(
                                                                                ConnectionStringsRepository.GetInstance(cnxClientPlatform));
            var cnxClientDW = connectionStringsManager.GetConnectionString(ConnectionStringType.ClientDW);
            bool useProtoBuffer = true;
            bool.TryParse(ConfigurationManager.AppSettings["UseProtobuffer"], out useProtoBuffer);

            // compose esgcomputation service url 
            var esgComputeUrl = ExtractESGComputationUrl();

            CompanyReportSerilog.Log.TryUrl(esgComputeUrl);
            // query service - compose request DTO
            var esgRatingsOptions = PrepareESGComputationRequestDto(options);

            var result = (ESGRatingsDto)null;
            IList<CorporateData> companies1 = new List<CorporateData>();
            IList<CorporateData> companies2 = new List<CorporateData>();
            IList<CorporateData> companies3 = new List<CorporateData>();
            var rep = new RepositoryCollection<CorporateData>(cnxClientDW);


            new Action[]
            {
                 // query service - compose request proper
                () => {result = ExecuteEsgComputationRequest(esgComputeUrl, esgRatingsOptions,  useProtoBuffer);},
                () =>// collect corporate data
                {

                    if (options.AllCompanies)
                    {
                        companies1 = rep.ReadAllWhere((cd) => cd.ComprehensiveEsgResearchState.ResearchState==ResearchState.ResearchAvailable);
                    }
                    else
                    {
                        if (options.SelectedCompaniesIds != null && options.SelectedCompaniesIds.Any())
                            companies1 = rep.ReadAllWhere((cd) => options.SelectedCompaniesIds.Contains(cd.Id) && cd.ComprehensiveEsgResearchState.ResearchState==ResearchState.ResearchAvailable).ToList();


                        if (options.SelectedPeerGroupNames != null && options.SelectedPeerGroupNames.Any())
                        {
                            var repo = new RepositoryCollection<PeerGroup>(cnxClientDW);
                            var pgs = repo.ReadAllWhere(pg => options.SelectedPeerGroupNames.Contains(pg.Name)).Select(x => x.Name);
                            if (pgs.Any())
                                companies2 = rep.ReadAllWhere((cd) => pgs.Contains(cd.PeerGroup) && cd.ComprehensiveEsgResearchState.ResearchState==ResearchState.ResearchAvailable);
                        }


                        if (options.SelectedIndustryGroupNames != null && options.SelectedIndustryGroupNames.Any())
                        {
                            var repo = new RepositoryCollection<SustainalyticsIndustryGroup>(cnxClientDW);
                            var igs = repo.ReadAllWhere(ig => options.SelectedIndustryGroupNames.Contains(ig.Name)).Select(x => x.Name);
                            if (igs.Any())
                                companies3 = rep.ReadAllWhere((cd) => igs.Contains(cd.SustainalyticsIndustryGroup) && cd.ComprehensiveEsgResearchState.ResearchState==ResearchState.ResearchAvailable);
                        }
                    }

                }
            }.RunInParallel(context.CancellationToken);

            var companies = companies1.Union(companies2).Union(companies3);

            var adapter = new ESGRatingsAdapter(result);

            // compose  a new excel file 
            var excelFile = ComposeExcelResult(adapter, companies, (string[])result.IndicatorsCodes.Clone());
            return ComposeLRTMResponse(options.FileName, excelFile);
        }

        private static object ComposeLRTMResponse(string fileName, IWorkbook excelFile)
        {
#if DEBUG

            var path = @"\AdminCheck\";
            if (!Directory.Exists(path))
                Directory.CreateDirectory(path);

            var filePath = path + fileName + ".xlsx";
            excelFile.SaveAs(filePath, FileFormat.OpenXMLWorkbook);
            var fi = new FileInfo(filePath);

            return new LongRunningTaskFileOnDiskResult
            {
                FileName = fileName + ".xlsx",
                MimeType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
                FilePath = filePath,
                ContentLength = fi.Length
            };
#endif


            return new LongRunningTaskFileInMemResult
            {
                EnforceInMemory = false,
                FileName = fileName + ".xlsx",
                MimeType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
                Contents = excelFile.SaveToMemory(FileFormat.OpenXMLWorkbook),
            };
        }

        private IWorkbook ComposeExcelResult(ESGRatingsAdapter adapter, IEnumerable<CorporateData> companies, string[] codes)
        {
            var excelFile = Factory.GetWorkbook();
            var emptySheet = excelFile.Worksheets[0].Name;

            Array.Sort(codes);
            var indicatorsCodeNames = codes.Where(x => x.StartsWith("G"))
                                            .Union(codes.Where(x => x.StartsWith("S")))
                                            .Union(codes.Where(x => x.StartsWith("E"))).ToList();


            var result1 = new List<ReportLine>();
            var result2 = new List<ReportLine>();
            var result3 = new List<ReportLine>();
            var result4 = new List<ReportLine>();

            foreach (var cd in companies)
            {
                var report1 = new ReportLine
                {
                    CapitalIQID = cd.CapitalIqId.ToString(),
                    CompanyName = cd.CompanyName,
                    PeerGroupName = cd.PeerGroup,
                    IndustryGroupName = cd.SustainalyticsIndustryGroup,
                    TemplateType = cd.TemplateType,
                    CompanyType = cd.CompanyType,
                    IndicatorsValue = new List<string>()
                };
                var report2 = new ReportLine
                {
                    CapitalIQID = cd.CapitalIqId.ToString(),
                    CompanyName = cd.CompanyName,
                    PeerGroupName = cd.PeerGroup,
                    IndustryGroupName = cd.SustainalyticsIndustryGroup,
                    TemplateType = cd.TemplateType,
                    CompanyType = cd.CompanyType,
                    IndicatorsValue = new List<string>()
                };
                var report3 = new ReportLine
                {
                    CapitalIQID = cd.CapitalIqId.ToString(),
                    CompanyName = cd.CompanyName,
                    PeerGroupName = cd.PeerGroup,
                    IndustryGroupName = cd.SustainalyticsIndustryGroup,
                    TemplateType = cd.TemplateType,
                    CompanyType = cd.CompanyType,
                    IndicatorsValue = new List<string>()
                };
                var report4 = new ReportLine
                {
                    CapitalIQID = cd.CapitalIqId.ToString(),
                    CompanyName = cd.CompanyName,
                    PeerGroupName = cd.PeerGroup,
                    IndustryGroupName = cd.SustainalyticsIndustryGroup,
                    TemplateType = cd.TemplateType,
                    CompanyType = cd.CompanyType,
                    IndicatorsValue = new List<string>()
                };
                var indicators = adapter.GetCompanyIndicatorData(cd.Id);





                var lowerindicators = indicatorsCodeNames.Select(x => x.ToLowerInvariant());
                // get  raw scores 
                foreach (var code in lowerindicators)
                {
                    if (indicators.ContainsKey(code))
                    {
                        report1.IndicatorsValue.Add(indicators[code].Score.ToString(CultureInfo.InvariantCulture));
                        report2.IndicatorsValue.Add(indicators[code].Weight.ToString(CultureInfo.InvariantCulture));
                        report3.IndicatorsValue.Add(indicators[code].WeightedScore.ToString(CultureInfo.InvariantCulture));
                    }
                    else
                    {
                        report1.IndicatorsValue.Add("N/A");
                        report2.IndicatorsValue.Add("N/A");
                        report3.IndicatorsValue.Add("N/A");

                    }
                }

                var performance = adapter.GetEsgData(cd.Id);
                var bestInclass = adapter.GetBestInClass(cd.Id);

                foreach (var k in Enum.GetValues(typeof(ReportLine.ScoresHeading)))
                {
                    switch ((ReportLine.ScoresHeading)k)
                    {
                        case ReportLine.ScoresHeading.CompanyScore: report4.IndicatorsValue.Add(performance.Score.ToString(CultureInfo.InvariantCulture)); break;

                        case ReportLine.ScoresHeading.EnvironmentScore: report4.IndicatorsValue.Add(performance.EnvironmentScore.ToString(CultureInfo.InvariantCulture)); break;
                        case ReportLine.ScoresHeading.SocialScore: report4.IndicatorsValue.Add(performance.SocialScore.ToString(CultureInfo.InvariantCulture)); break;
                        case ReportLine.ScoresHeading.GovernanceScore: report4.IndicatorsValue.Add(performance.GovernanceScore.ToString(CultureInfo.InvariantCulture)); break;

                        case ReportLine.ScoresHeading.PreparednessScore: report4.IndicatorsValue.Add(performance.PreparednessScore.ToString(CultureInfo.InvariantCulture)); break;
                        case ReportLine.ScoresHeading.DisclosureScore: report4.IndicatorsValue.Add(performance.DisclosureScore.ToString(CultureInfo.InvariantCulture)); break;
                        case ReportLine.ScoresHeading.QuantitativeScore: report4.IndicatorsValue.Add(performance.QuantitativePerformanceScore.ToString(CultureInfo.InvariantCulture)); break;
                        case ReportLine.ScoresHeading.QualitativeScore: report4.IndicatorsValue.Add(performance.QualitativePerformanceScore.ToString(CultureInfo.InvariantCulture)); break;

                        case ReportLine.ScoresHeading.AverageOfGroup: report4.IndicatorsValue.Add(performance.GroupAvg.ToString(CultureInfo.InvariantCulture)); break;

                        case ReportLine.ScoresHeading.RankWithinGroup: report4.IndicatorsValue.Add(performance.Rank.ToString()); break;
                        case ReportLine.ScoresHeading.PercentileRankWithinGroup: report4.IndicatorsValue.Add(performance.Percentile.ToString(CultureInfo.InvariantCulture)); break;

                        case ReportLine.ScoresHeading.RankWithinGroupEnvironment: report4.IndicatorsValue.Add(performance.EnvironmentRank.ToString(CultureInfo.InvariantCulture)); break;
                        case ReportLine.ScoresHeading.RankWithinGroupSocial: report4.IndicatorsValue.Add(performance.SocialRank.ToString(CultureInfo.InvariantCulture)); break;
                        case ReportLine.ScoresHeading.RankWithinGroupGovernance: report4.IndicatorsValue.Add(performance.GovernanceRank.ToString(CultureInfo.InvariantCulture)); break;

                        case ReportLine.ScoresHeading.PercentileWithinGroupEnvironment: report4.IndicatorsValue.Add(performance.EnvironmentPercentile.ToString(CultureInfo.InvariantCulture)); break;
                        case ReportLine.ScoresHeading.PercentileWithinGroupSocial: report4.IndicatorsValue.Add(performance.SocialPercentile.ToString(CultureInfo.InvariantCulture)); break;
                        case ReportLine.ScoresHeading.PercentileWithinGroupGovernance: report4.IndicatorsValue.Add(performance.GovernancePercentile.ToString(CultureInfo.InvariantCulture)); break;

                        case ReportLine.ScoresHeading.MaxWithinGroupPreparedeness: report4.IndicatorsValue.Add(bestInclass.PreparednessMaxInGroup.ToString(CultureInfo.InvariantCulture)); break;
                        case ReportLine.ScoresHeading.MaxWithinGroupDisclosure: report4.IndicatorsValue.Add(bestInclass.DisclosureMaxInGroup.ToString(CultureInfo.InvariantCulture)); break;
                        case ReportLine.ScoresHeading.MaxWithinGroupQuantitative: 
                        case ReportLine.ScoresHeading.MaxWithinGroupQualitative:
                            report4.IndicatorsValue.Add(bestInclass.QualitativePerformanceMaxInGroup.ToString(CultureInfo.InvariantCulture)); break;
                    }
                }

                result1.Add(report1);
                result2.Add(report2);
                result3.Add(report3);
                result4.Add(report4);
            }

            result1 = result1.OrderBy(m => m.PeerGroupName).ThenBy(m => m.CompanyName).ToList();
            result1.Insert(0, ReportLine.Header(indicatorsCodeNames));
            result2 = result2.OrderBy(m => m.PeerGroupName).ThenBy(m => m.CompanyName).ToList();
            result2.Insert(0, ReportLine.Header(indicatorsCodeNames));
            result3 = result3.OrderBy(m => m.PeerGroupName).ThenBy(m => m.CompanyName).ToList();
            result3.Insert(0, ReportLine.Header(indicatorsCodeNames));
            result4 = result4.OrderBy(m => m.PeerGroupName).ThenBy(m => m.CompanyName).ToList();
            result4.Insert(0, ReportLine.HeaderScores());



            AddWorkSheet("Raw scores", excelFile, result1);
            AddWorkSheet("Weights", excelFile, result2);
            AddWorkSheet("Ratings", excelFile, result3);
            AddWorkSheet("Scores", excelFile, result4);

            excelFile.Worksheets[emptySheet].Delete();
            return excelFile;
        }

        private static string ExtractESGComputationUrl()
        {
            var localUrl = (string)ConfigurationManager.AppSettings["ESGComputeURL"];
            if (localUrl == null)
                localUrl = "http://localhost:10280/esgcomputingserv/api/v1/esgcompute";
            return localUrl;
        }

        public static ESGRatingsDto ExecuteEsgComputationRequest(string url, ESGRatingsOptionsDto esgRatingsOptions, bool useProtoBuffer = false)
        {
            var esgClient = new HttpClient();

            MediaTypeFormatter mediaFormatter;
            var jsonFormatter = new JsonMediaTypeFormatter { SerializerSettings = { ContractResolver = new CamelCasePropertyNamesContractResolver() } };
            if (useProtoBuffer)
            {
                mediaFormatter = (MediaTypeFormatter)(new ProtoBufFormatter { });
                esgClient.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/x-protobuf"));
            }
            else
            {
                mediaFormatter = (MediaTypeFormatter)(jsonFormatter);
                esgClient.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
            }

            long requestDuration;
            long deserializationDuration;
            long contentSize;

            var sw = Stopwatch.StartNew();
            sw.Start();
            var esgResponse = esgClient.PostAsync(url, esgRatingsOptions, jsonFormatter).Result;
            esgResponse.EnsureSuccessStatusCode();
            sw.Stop();
            requestDuration = sw.ElapsedMilliseconds;


            sw.Restart();
            var res = esgResponse.Content.ReadAsAsync<ESGRatingsDto>(new[] { mediaFormatter }).Result;
            sw.Stop();
            deserializationDuration = sw.ElapsedMilliseconds;


            contentSize = esgResponse.Content.Headers.ContentLength ?? 0;

            CompanyReportSerilog.Log.CustomCompanyExcelStatus(url, requestDuration, deserializationDuration, contentSize);
#if DEBUG

            System.Diagnostics.Trace.TraceInformation("use Protocol buffer:, {0},request duration, {1}, deserialization duration, {2}, content size, {3}",
               useProtoBuffer, requestDuration, deserializationDuration, contentSize);
#endif
            return res;
        }

        private static ESGRatingsOptionsDto PrepareESGComputationRequestDto(TestESGRatingInDto options)
        {

            var esgRatingsOptions = new ESGRatingsOptionsDto
            {
                MatrixId = options.SelectedMatrixId,
                RefUnivId = options.SelectedReferenceUniverseId,
                IncludeIndWeightedScores = true,
                IncludeRatings = true,
                IncludeAggregatedScores = true,
                IncludeWeights = true,
                IncludeAggregatedWeights = true,
                IncludeTopPerformersInfo = false,
            };
            if (options.SelectedComparativeGroup == ComparativeGroupSelection.Industry)
                esgRatingsOptions.GroupBy = ComparativeGroupEnum.IndustryGroup;

            if (options.SelectedTemplateType == TemplateTypeSelection.Type_A)
                esgRatingsOptions.TemplateType = TemplateTypeEnum.TypeA;

            if (options.SelectedTemplateType == TemplateTypeSelection.Type_B)
                esgRatingsOptions.TemplateType = TemplateTypeEnum.TypeB;


            if (options.SelectedCompanyType == CompanyTypeSelection.Private)
                esgRatingsOptions.CompanyType = CompanyTypeEnum.Private;

            if (options.SelectedCompanyType == CompanyTypeSelection.Public)
                esgRatingsOptions.CompanyType = CompanyTypeEnum.Public;

            if (!options.AllCompanies)
            {
                esgRatingsOptions.CompaniesIds = options.SelectedCompaniesIds;
            }
            esgRatingsOptions.CompaniesIds = null;

            esgRatingsOptions.PeerGroups = options.SelectedPeerGroupNames;
            esgRatingsOptions.IndustryGroups = options.SelectedIndustryGroupNames;

            //Todo: replace string by ids for peerGroup and industryGroups



            return esgRatingsOptions;
        }

        private void AddWorkSheet(string name, IWorkbook wbook, IEnumerable<ReportLine> data)
        {
            wbook.Worksheets.Add();
            var count = wbook.Sheets.Count;
            wbook.Worksheets[count - 1].Name = name;
            wbook.Worksheets[count - 1].Select();

            var index = 0;
            foreach (var d in data)
            {
                AddLine(wbook.ActiveWorksheet, index++, d);
            }

            wbook.ActiveWorksheet.UsedRange.WrapText = true;
            wbook.ActiveWorksheet.Cells[0, 0].EntireRow.ColumnWidth = 16;
            wbook.ActiveWorksheet.Cells[0, 0].EntireRow.AutoFilter();
            wbook.ActiveWorksheet.Cells[0, 0].EntireRow.Font.Bold = true;
        }

        private void AddLine(IWorksheet workSheet, int rowIndex, ReportLine d)
        {
            var columnIndex = 0;
            workSheet.Cells[rowIndex, columnIndex++].Value = d.CapitalIQID;
            workSheet.Cells[rowIndex, columnIndex++].Value = d.CompanyName;
            workSheet.Cells[rowIndex, columnIndex++].Value = d.PeerGroupName;
            workSheet.Cells[rowIndex, columnIndex++].Value = d.IndustryGroupName;
            workSheet.Cells[rowIndex, columnIndex++].Value = d.TemplateType;
            workSheet.Cells[rowIndex, columnIndex++].Value = d.CompanyType;

            foreach (var s in d.IndicatorsValue)
            {
                workSheet.Cells[rowIndex, columnIndex++].Value = s;
            }
        }
    }
}
